package com.atguigu.test02;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * @author haoranlu
 * @project IntelliJ IDEA
 *
 * 如何实现批处理呢？
 * (1)执行时,不能直接调用pst.executeUpdate();
 * 那么需要:
 * ①pst.addBatch();
 * ②pst.executeBatch();
 *
 * (2)mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持
 * 在url后面加一个  rewriteBatchedStatements=true
 *
 * url：jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
 *
 * 如果有三个参数: &&&
 *      jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncodeing=utf-8&rewriteBatchedStatements=true
 *
 */
public class BatchTest {
    @Test
    public void test03() throws Exception{
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
        Connection conn = DriverManager.getConnection(url,"root","123456");

        String sql = "insert into t_department values(null,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);

        for (int i = 1; i <= 1000; i++) {
            pst.setString(1,"发钱部门"+i);
            pst.setString(2,"发工资"+i);
            pst.addBatch();//添加到批处理中，先累积下来，本质底层有一个缓冲区，先缓冲所有要执行的sql

        }
        //int[] executeBatch = pst.executeBatch();//这里如果你需要返回结果就接收
        pst.executeBatch();
        pst.close();
        conn.close();
        long end = System.currentTimeMillis();
        System.out.println("耗时:"+(end-start));
    }
    @Test
    public void test02() throws Exception{
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test";
        Connection conn = DriverManager.getConnection(url,"root","123456");

        String sql = "insert into t_department values(null,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);

        for (int i = 1; i <= 1000; i++) {
            pst.setString(1,"发钱部门"+i);
            pst.setString(2,"发工资"+i);
            pst.addBatch();//添加到批处理中，先累积下来，本质底层有一个缓冲区，先缓冲所有要执行的sql

        }
        //int[] executeBatch = pst.executeBatch();//这里如果你需要返回结果就接收
        pst.executeBatch();
        pst.close();
        conn.close();
        long end = System.currentTimeMillis();
        System.out.println("耗时:"+(end-start));
    }
    @Test
    public void test01() throws Exception{
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");

        String url = "jdbc:mysql://localhost:3306/test";
        Connection conn = DriverManager.getConnection(url,"root","123456");

        String sql = "insert into t_department values(null,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);

        for (int i = 1; i <= 1000; i++) {
            pst.setString(1,"发钱部门"+i);
            pst.setString(2,"发工资"+i);
            pst.executeUpdate();
        }

        pst.close();
        conn.close();
        long end = System.currentTimeMillis();
        System.out.println("耗时:"+(end-start));
    }
}
